package com.example.demo33.mode;

import com.example.demo33.entity.Item;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.math.BigDecimal;

public interface ItemRepository extends JpaRepository<Item, Long> {

    // 1. 基础查询：按用户ID查询
    Page<Item> findByUserId(Long userId, Pageable pageable);

    // 2. 状态查询（缺失的关键方法）
    @Query("SELECT i FROM Item i WHERE i.user.id = :userId AND i.soldQuantity = 0")
    Page<Item> findByUserIdAndHolding(@Param("userId") Long userId, Pageable pageable);

    @Query("SELECT i FROM Item i WHERE i.user.id = :userId AND i.soldQuantity > 0 AND i.soldQuantity < i.totalQuantity")
    Page<Item> findByUserIdAndPartiallySold(@Param("userId") Long userId, Pageable pageable);

    @Query("SELECT i FROM Item i WHERE i.user.id = :userId AND i.soldQuantity = i.totalQuantity")
    Page<Item> findByUserIdAndSoldOut(@Param("userId") Long userId, Pageable pageable);

    // 3. 搜索+状态组合查询
    @Query("SELECT i FROM Item i WHERE i.user.id = :userId AND LOWER(i.name) LIKE LOWER(CONCAT('%', :search, '%'))")
    Page<Item> findByUserIdAndNameContaining(
            @Param("userId") Long userId,
            @Param("search") String search,
            Pageable pageable
    );

    @Query("SELECT i FROM Item i WHERE i.user.id = :userId AND LOWER(i.name) LIKE LOWER(CONCAT('%', :search, '%')) AND i.soldQuantity = 0")
    Page<Item> findByUserIdAndNameContainingAndHolding(
            @Param("userId") Long userId,
            @Param("search") String search,
            Pageable pageable
    );

    @Query("SELECT i FROM Item i WHERE i.user.id = :userId AND LOWER(i.name) LIKE LOWER(CONCAT('%', :search, '%')) AND i.soldQuantity > 0 AND i.soldQuantity < i.totalQuantity")
    Page<Item> findByUserIdAndNameContainingAndPartiallySold(
            @Param("userId") Long userId,
            @Param("search") String search,
            Pageable pageable
    );

    @Query("SELECT i FROM Item i WHERE i.user.id = :userId AND LOWER(i.name) LIKE LOWER(CONCAT('%', :search, '%')) AND i.soldQuantity = i.totalQuantity")
    Page<Item> findByUserIdAndNameContainingAndSoldOut(
            @Param("userId") Long userId,
            @Param("search") String search,
            Pageable pageable
    );

    // 统计用户的饰品总数
    @Query("SELECT COUNT(i) FROM Item i WHERE i.user.id = :userId")
    int countByUserId(@Param("userId") Long userId);



    // 计算用户总盈利
    @Query("SELECT COALESCE(SUM((i.sellPrice * i.soldQuantity) - (i.buyPrice * i.soldQuantity)), 0) FROM Item i " +
            "WHERE i.user.id = :userId AND i.soldQuantity > 0")
    BigDecimal calculateTotalProfit(@Param("userId") Long userId);

    // 统计用户的总购入数量
    @Query("SELECT COALESCE(SUM(i.totalQuantity), 0) FROM Item i WHERE i.user.id = :userId")
    Integer sumTotalQuantityByUserId(@Param("userId") Long userId);

    // 统计用户的总售出数量
    @Query("SELECT COALESCE(SUM(i.soldQuantity), 0) FROM Item i WHERE i.user.id = :userId")
    Integer sumSoldQuantityByUserId(@Param("userId") Long userId);

    // 计算用户总购入成本
    @Query("SELECT COALESCE(SUM(i.buyPrice * i.totalQuantity), 0) FROM Item i WHERE i.user.id = :userId")
    BigDecimal calculateTotalBuyCost(@Param("userId") Long userId);

    // 计算用户总售出收入
    @Query("SELECT COALESCE(SUM(i.sellPrice * i.soldQuantity), 0) FROM Item i " +
            "WHERE i.user.id = :userId AND i.soldQuantity > 0")
    BigDecimal calculateTotalSellRevenue(@Param("userId") Long userId);

    // 计算持有数量 = 每件饰品的(总数量 - 已售出数量)之和
    @Query("SELECT COALESCE(SUM(i.totalQuantity - i.soldQuantity), 0) FROM Item i WHERE i.user.id = :userId")
    Integer calculateHoldingCount(@Param("userId") Long userId);

    // 计算有售出记录的饰品的总购入成本
    @Query("SELECT SUM(i.buyPrice * i.totalQuantity) FROM Item i " +
            "WHERE i.user.id = :userId AND i.soldQuantity > 0")
    BigDecimal calculateTotalBuyCostForSoldItems(@Param("userId") Long userId);
}